﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using StartStop.ServiceResources; 
using System.Data.SqlClient;
using System.Data; 

namespace StartStop.Helpers
{
    public class Ranking
    {

        public const string LiveConnectionString = "data source=v2.startstop.me;initial catalog=startstop;persist security info=True;user id=sa;password=PASSWORD;";


        public static StartStop.ServiceResources.ListOfRanking ReturnUserRanksForSpecificDetailStat(Int64 DetailStatID)
        {
            // Load the overview
            //StatOverView oOverview 

            // First load the detail stat
            ServiceResources.StatDescription oStat = Helpers.DetailedStat.LoadDetailStat(DetailStatID);
            ServiceResources.StatOverView oOverview = Helpers.DetailedStatOverview.LoadOverview(oStat.OverviewID);

            ServiceResources.ListOfRanking oRanking = new ListOfRanking();
            oRanking.Overview = oOverview;
            oRanking.SpecificStatDescription = oStat;
            ServiceResources.StatRanking oStatRank = new StatRanking();

            List<StatRanking> oList = new List<StatRanking>(); 
            // Next we do a select on the detail stat and order by ascending. 

            SqlConnection oConn = new SqlConnection(LiveConnectionString); 
            SqlCommand cmd = new SqlCommand(@"
                                                select UserName, DetailOfStat, [COUNT], ll.UserID
                                                from (select l.*, 
                                                             row_number() over (partition by [UserID], [DetailStatID]
                                                                                order by [CreatedOn] desc) rn
                                                      from DetailStatUserLog l) ll
                                                join ssUsers u on ll.[UserId] = u.[UserId]
                                                Join DetailStat s on ll.[DetailStatId] = s.[DetailStatId]
                                                where ll.rn=1 and ll.DetailStatID = @DetailStatID
                                                order by [Count] desc
            ", oConn);

            cmd.Parameters.AddWithValue("@DetailStatID", DetailStatID);
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);
            int i = 0; 
            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                oStatRank = new StatRanking();

                i++;
                oStatRank.Rank = i; 
                oStatRank.Count = int.Parse(oRow["count"].ToString()); 
                oStatRank.Username = oRow["UserName"].ToString(); 
                oStatRank.UserID = Int64.Parse(oRow["UserID"].ToString());
                oList.Add(oStatRank); 
                
            }

            oRanking.Rankings = oList; 

            return oRanking; 
        }

    }
}
